"""
@author: xiangping
@contact: xiangpingbu@gmail.com
@time: 2020/1/10 4:46 下午
@file: shaojie_dao_service
@Desc:
"""
import datetime

from sqlalchemy import and_, func

from app.database import convert
from app.exts import ironman_redis, mysql_pool
from app.models.po.shaojie.shaojie_model_input_po import ShaoJieInputPO
from app.models.po.shaojie.shaojie_model_input_simulate_po import ShaoJieInputSimulatePO
from app.models.po.shaojie.shaojie_predict_po import ShaoJiePredictPO
from typing import List


def shaojie_predict_limited_select(**kwargs):
    limit = kwargs.get('limit', 1000)
    order_field = kwargs.get('order_field', None)
    order = kwargs.get('order', None)
    latest_id = kwargs.get('latest_id', None)

    if order_field is None or order is None:
        if latest_id is None:
            ret = convert.query(ShaoJiePredictPO,
                                limit=limit)
        else:
            ret = convert.query(ShaoJiePredictPO,
                                ShaoJiePredictPO.ID > latest_id,
                                limit=limit)
    else:
        ret = convert.query(ShaoJiePredictPO,
                            order_by=getattr(getattr(ShaoJiePredictPO, order_field), order)(),
                            limit=limit)
    return ret.all()


def predict_all_select(**kwargs):
    start_time = kwargs.get('start_time', None)
    end_time = kwargs.get('end_time', None)
    order_field = kwargs.get('order_field', None)
    order = kwargs.get('order', None)
    sample_rate = kwargs.get('sample_rate', None)

    ret = convert.query(ShaoJiePredictPO,
                        ShaoJiePredictPO.ID % sample_rate == 0,
                        and_(ShaoJiePredictPO.DateTime > start_time, ShaoJiePredictPO.DateTime < end_time),
                        order_by=getattr(getattr(ShaoJiePredictPO, order_field), order)())
    return ret.all()


def select_latest_input(back_time_in_minutes):
    '''
    只读取当前时间 之前 back_time_in_minutes 分钟的数据
    :param back_time:
    :return:
    '''

    end_time = datetime.datetime.now()

    # ad-hoc
    # end_time = datetime.datetime.strptime("2020-02-17 23:55:00", "%Y-%m-%d %H:%M:%S")
    start_time = end_time - datetime.timedelta(minutes=back_time_in_minutes)
    order_field = 'DateTime'
    order = 'desc'

    ret = convert.query(ShaoJieInputPO,
                        and_(ShaoJieInputPO.DateTime > start_time, ShaoJieInputPO.DateTime < end_time),
                        order_by=getattr(getattr(ShaoJieInputPO, order_field), order)())

    return ret.first()


def select_latest_input_at(query_time, back_time_in_minutes):
    '''
    读取query_time 之前 back_time_in_minutes 分钟的数据
    :param back_time:
    :return:
    '''
    end_time = query_time
    start_time = end_time - datetime.timedelta(minutes=back_time_in_minutes)
    order_field = 'DateTime'
    order = 'desc'

    ret = convert.query(ShaoJieInputPO,
                        and_(ShaoJieInputPO.DateTime > start_time, ShaoJieInputPO.DateTime < end_time),
                        order_by=getattr(getattr(ShaoJieInputPO, order_field), order)())

    return ret.first()


def predict_result_insert(loc_temp_predict_po):
    convert.add_one(loc_temp_predict_po)


def select_simulate_input(end_time, back_time_in_minutes=1):
    if end_time is None:
        end_time = datetime.datetime.now()

    start_time = end_time - datetime.timedelta(minutes=back_time_in_minutes)
    order_field = 'DateTime'
    order = 'desc'

    ret = convert.query(ShaoJieInputSimulatePO,
                        and_(ShaoJieInputSimulatePO.DateTime > start_time, ShaoJieInputSimulatePO.DateTime < end_time),
                        order_by=getattr(getattr(ShaoJieInputSimulatePO, order_field), order)())
    return ret.first()


def insert_input_po(input_po):
    """
    保存输入参数
    :param input_po:
    :return: void
    """
    convert.add_one(input_po)


def get_latest_input_cache():
    input_po = ShaoJieInputPO()
    input_po.GMT_CREATE = datetime.datetime.now()
    value_dict = ironman_redis.hget_all_str_decode('shaojie')
    # update_time_dict = ironman_redis.hget_all_str_decode('update')
    update_time = ironman_redis.get("latestUpdate")
    input_po.DateTime = datetime.datetime.strptime(update_time, "%Y-%m-%d %H:%M:%S")
    for v in input_po.__dir__():
        if v.startswith('CG'):
            # if v in update_time_dict:
            #     input_po.DateTime = datetime.datetime.strptime(update_time_dict[v], "%Y-%m-%d %H:%M:%S")
            input_po.__setattr__(v, float(value_dict[v]) if v in value_dict else None)
    return input_po


def get_shaojie_group_data(start, end, agg_func, group_field, group_level):
    """
    :param table: 查询表
    :param start: 开始时间
    :param end: 结束时间
    :param agg_func: 聚合方法
    :param group_filed: 聚合作用字段
    :param group_level: 聚合级别
    :return:
    """

    group_sql = None
    if group_level == 'day':
        group_sql = "year(`GMT_CREATE`),month(`GMT_CREATE`),day(`GMT_CREATE`)"
    if group_level == 'hour':
        group_sql = "month(`GMT_CREATE`),day(`GMT_CREATE`),hour(`GMT_CREATE`)"
    if group_level == 'min':
        group_sql = "day(`GMT_CREATE`),hour(`GMT_CREATE`),min(`GMT_CREATE`)"

    # table = ShaoJiePredictPO
    # list: List = db.session.query(agg_func(getattr(table, group_filed)).label(group_filed),
    #                               func.any_value(getattr(table, 'GMT_CREATE'))) \
    #     .filter(getattr(table, 'GMT_CREATE').between(start, end)) \
    #     .group_by(group_level(getattr(table, 'GMT_CREATE'))).all()

    conn, cursor = mysql_pool.connect(db="ironman")
    cursor.execute("""
    select {}({}) as {},any_value(`GMT_CREATE`) as GMT_CREATE from cg_sj_sjj_sj02_predict 
    where `GMT_CREATE` >='{}' and GMT_CREATE <='{}' 
     group by {}
    """.format(agg_func,group_field,group_field,start,end,group_sql))

    results = cursor.fetchall()

    mysql_pool.connect_close(conn,cursor)

    r: List[dict] = results
    return r


def shaojie_history_between_query(start, end, left='close', right='close', order_by=None) \
        -> List[ShaoJiePredictPO]:
    if left == "close" and right == "close":
        ret = convert.query(ShaoJiePredictPO, ShaoJiePredictPO.DateTime.between(start, end), order_by=order_by)
    elif left == "open" and right == "open":
        ret = convert.query(ShaoJiePredictPO,
                            and_(ShaoJiePredictPO.DateTime > start, ShaoJiePredictPO.DateTime < end), order_by=order_by)
    elif left == "open" and right == "close":
        ret = convert.query(ShaoJiePredictPO,
                            and_(ShaoJiePredictPO.DateTime > start, ShaoJiePredictPO.DateTime <= end),
                            order_by=order_by)
    elif left == "close" and right == "open":
        ret = convert.query(ShaoJiePredictPO,
                            and_(ShaoJiePredictPO.DateTime >= start, ShaoJiePredictPO.DateTime < end),
                            order_by=order_by)
    return ret
